
* 1) Open repeat sales in Land Reg (matched with HTB) ----

use "${datadir}\landreg_2013-2020_matched_big", replace

bysort unit_id (dated transaction_id): gen nextyear = year[_n+1]
// sorted also by transaction_id to make it unambiguous

gen htb = !mi(schemeid)



* 2) Add in redemptions data ----

merge m:1 schemeid using ///
	"0 - Shared Appreciation\data\HCA_redemptions_all", keep(1 3) ///
	keepusing(Datepurchased HCACash OriginalHCAshare Transactiontype ///
	Salepriceagree Completion merge_htb_main) gen(merge_repaid)

gen stair = regexm(Transaction, "staircasing") 



* 3) Which staircased properties have a sale afterwards? ----

bysort unit_id (dated transaction_id): gen stair_sold = !mi(year[_n+1]) & ///
	stair & mofd(Completion) < datem[_n+1]


// Non annualised
bysort unit_id (dated transaction_id): gen pratio_stair_sold = price[_n+1]/Salepriceagreed ///
	if stair_sold

// Annualised
bysort unit_id (dated transaction_id): ///
	gen diff_stair_sold = dated[_n+1] - Completion if stair_sold

gen diff_stair_sold_a = diff_stair_sold / 365

gen dpa_stair_sold = (pratio_stair_sold^(1/diff_stair_sold_a) - 1) * 100

sum dpa_stair_sold 
sum dpa_stair_sold if diff_stair_sold_a > 0.5
sum dpa_stair_sold if diff_stair_sold_a < 1/2


// non- annualised
sum pratio_stair_sold if diff_stair_sold < 180

// average distance in days
sum diff_stair_sold if diff_stair_sold_a < 1/2




* 4) Which staircased properties have been valued the same as the purchase price? ----

gen zeroval = Salepriceagree == price & stair

// annualised
sum dpa_stair_sold if stair_sold & zeroval
sum dpa_stair_sold if diff_stair_sold_a > 0.5 & stair_sold & zeroval

sum dpa_stair_sold if diff_stair_sold_a < 1/2 & stair_sold & zeroval

// Non annualised
sum pratio_stair_sold if stair_sold & zeroval & diff_stair_sold < 180

// average distance in days
sum diff_stair_sold if diff_stair_sold_a < 1/2 & stair_sold & zeroval
